﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using WL_Store.Model;

namespace WL_Store.DAL
{
    /// <summary>
    /// 会员信息
    /// </summary>
   public  class VipInfoDAL
    {
        /// <summary>
        /// 查询会员信息
        /// </summary>
        /// <returns></returns>
        //public DataTable GetVipInfos()
        //{
        //    string sql = "select VIPInfo.vipId,VIPInfo.vipcard,VIPInfo.vipname,VIPInfo.sex,VIPInfo.phone,VIPInfo.address,VIPInfo.registertime,VIPInfo.grade from VIPInfo";

        //    return Dbhelper.ExecuteTable(sql);
        //}

        public List<VipInfoModel> GetVipInfos(VipInfoSearchModel searchModel)
        {
            string sql = "select VIPInfo.vipId,VIPInfo.vipcard,VIPInfo.vipname,VIPInfo.sex,VIPInfo.phone,VIPInfo.address,VIPInfo.registertime,VIPInfo.grade from VIPInfo";
            //构造where的泛型集合
            List<string> listwhere = new List<string>();
            //构造参数化查询数组的泛型集合
            List<SqlParameter> listparms = new List<SqlParameter>();
            //判断多条件查询的条件会员卡号是否输入
            if (!string.IsNullOrWhiteSpace(searchModel.vipcard))
            {
                listwhere.Add("VIPInfo.vipcard=@vipcard");
                listparms.Add(new SqlParameter( "@vipcard",searchModel.vipcard));
            }
            //判断多条件查询的条件会员姓名是否输入
            if (!string.IsNullOrWhiteSpace(searchModel.vipname))
            {
                listwhere.Add("VIPInfo.vipname=@vipname");
                listparms.Add(new SqlParameter("@vipname", searchModel.vipname));
            }
            //判断多条件查询的条件会员性别是否输入
            if (searchModel.sex>-1)
            {
                listwhere.Add("VIPInfo.sex=@sex");
                listparms.Add(new SqlParameter("@sex", searchModel.sex));
            }
            //判断检索会员信息是否为空
            if (listwhere.Count>0)
            {
                sql += " where " + string.Join(" and ", listwhere.ToArray());
            }


            SqlDataReader readers = Dbhelper.ExecuteReader(sql,listparms.ToArray());

            List<VipInfoModel> list = new List<VipInfoModel>();

            if (readers.HasRows)
            {
                while (readers.Read())
                {
                    VipInfoModel model = new VipInfoModel();
                    model.vipId = Convert.ToInt32(readers["vipId"]);
                    model.vipcard = Convert.ToInt32(readers["vipcard"]);
                    model.vipname = readers["vipname"].ToString();
                    model.sex = Convert.ToBoolean(readers["sex"]);
                    model.phone = readers["phone"].ToString();
                    model.address = readers["address"].ToString();
                    model.registertime = Convert.ToDateTime(readers["registertime"]);
                    model.grade = readers["grade"].ToString();
                    list.Add(model);
                }
                readers.Close();
            }
            return list;
        }


        /// <summary>
        /// 添加VIP用户信息 返回true：添加成功 返回false：添加失败
        /// </summary>
        /// <param name="vipInfo"></param>
        /// <returns></returns>
        public bool AddVipInfos(VipInfoModel vipInfo) {
            string sql = @"insert into VIPInfo(
vipId,vipcard,vipname,sex,phone,address,registertime,grade) values
(@vipId,@vipcard,@vipname,@sex,@phone,@address,@registertime,@grade)";
            //构造参数化查询
            SqlParameter[] parameters = {
            new SqlParameter("@vipId",vipInfo.vipId),
            new SqlParameter("@vipcard",vipInfo.vipcard),
            new SqlParameter("@vipname",vipInfo.vipname),
            new SqlParameter("@sex",vipInfo.sex),
            new SqlParameter("@phone",vipInfo.phone),
            new SqlParameter("@address",vipInfo.address),
            new SqlParameter("@registertime",vipInfo.registertime),
            new SqlParameter("@grade",vipInfo.grade),
            };
            return Dbhelper.ExecuteNonQuery(sql, parameters) > 0;


        }
        /// <summary>
        /// 删除会员信息 返回true：删除成功；返回false：删除失败 
        /// </summary>
        /// <param name="commodityID"></param>
        /// <returns></returns>
        public bool DeleteVipInfoso(int vipId)
        {
            string sql = @"delete from VIPInfo where vipId=@vipId";
            SqlParameter[] parameters = {
            new SqlParameter("@vipId",vipId)
            };
            return Dbhelper.ExecuteNonQuery(sql, parameters) > 0;
        }

        /// <summary>
        /// 根据会员Id查询会员信息
        /// </summary>
        /// <param name="vipId"></param>
        /// <returns></returns>
        public VipInfoModel VipInfo(int vipId)
        {
            string sql = "select*from VIPInfo where vipId=@vipId";
            SqlParameter[] parameters = { 
            new SqlParameter("@vipId",vipId)
            };
            SqlDataReader readers = Dbhelper.ExecuteReader(sql,parameters);

            if (readers.HasRows)
            {
                while (readers.Read())
                {
                    VipInfoModel model = new VipInfoModel();
                    model.vipId = Convert.ToInt32(readers["vipId"]);
                    model.vipcard = Convert.ToInt32(readers["vipcard"]);
                    model.vipname = readers["vipname"].ToString();
                    model.sex = Convert.ToBoolean(readers["sex"]);
                    model.phone = readers["phone"].ToString();
                    model.address = readers["address"].ToString();
                    model.registertime = Convert.ToDateTime(readers["registertime"]);
                    model.grade = readers["grade"].ToString();
                    return model;
                }
                readers.Close();
            }
            return null;

        }
        /// <summary>
        /// 根据vipId修改会员信息 返回true:修改成功；返回false：修改失败
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdatevipInfo(VipInfoModel model)
        {
            string sql = @"update VIPInfo set vipcard=@vipcard,vipname=@vipname,sex=@sex,phone=@phone,address=@address,grade=@grade where vipId=@vipId";

            SqlParameter[] parameters = { 
            new SqlParameter("@vipcard",model.vipcard),
            new SqlParameter("@vipname",model.vipname),
            new SqlParameter("@sex",model.sex),
            new SqlParameter("@phone",model.phone),
            new SqlParameter("@address",model.address),
            new SqlParameter("@grade",model.grade),
            new SqlParameter("@vipId",model.vipId),
            };
            return Dbhelper.ExecuteNonQuery(sql, parameters) > 0;
        }




        /// <summary>
        /// 查询所有信息（proc）(分页)
        /// </summary>
        /// <param name="searchModel"></param>
        /// <returns></returns>
        public List<VipInfoModel> GetVipInfosProc(VipInfoSearchModel searchModel,int PageIndex, int PageSize, out int TotalCount)
        {
            TotalCount = 0;
            string sql = "PROC_QueryVIPInfo";
            SqlParameter[] parameters = { 
            new SqlParameter("@vipcard",searchModel.vipcard),
            new SqlParameter("@vipname",searchModel.vipname),
            new SqlParameter("@sex",searchModel.sex),
            new SqlParameter("@StartRegistertime",searchModel.StartRegistertime.HasValue?searchModel.StartRegistertime:(object)DBNull.Value),
            new SqlParameter("@EndRegistertime",searchModel.EndRegistertime.HasValue?searchModel.EndRegistertime:(object)DBNull.Value),
            new SqlParameter("@VipGadeId",searchModel.VipGadeId),
            new SqlParameter("@PageIndex",PageIndex),
            new SqlParameter("@PageSize",PageSize),
            new SqlParameter("@TotalCount",TotalCount)

            };

            //为存储过程中输入类型参数设定为output
            parameters[8].Direction = ParameterDirection.Output;

            SqlDataReader readers = Dbhelper.ExecuteReaderProc(sql, parameters);

            List<VipInfoModel> list = new List<VipInfoModel>();

            if (readers.HasRows)
            {
                while (readers.Read())
                {
                    VipInfoModel model = new VipInfoModel();
                    model.vipId = Convert.ToInt32(readers["VipGadeId"]);
                    model.vipcard = Convert.ToInt32(readers["vipcard"]);
                    model.vipname = readers["vipname"].ToString();
                    model.sex = Convert.ToBoolean(readers["sex"]);
                    model.phone = readers["phone"].ToString();
                    model.address = readers["address"].ToString();
                    model.registertime = Convert.ToDateTime(readers["registertime"]);
                    //model.grade = readers["grade"].ToString();
                    list.Add(model);
                }
                readers.Close();
                //获取存储过程中的输出类型参数的值，一定要在游标关闭之后！！！！！
                TotalCount = Convert.ToInt32(parameters[8].Value);
            }
            return list;
        }


        /// <summary>
        /// 添加VIP用户信息 返回true：添加成功 返回false：添加失败(proc)
        /// </summary>
        /// <param name="vipInfo"></param>
        /// <returns></returns>
        public bool AddVipInfosProc(VipInfoModel vipInfo)
        {
            string sql = @"PROC_AddVIPInfo";
            //构造参数化查询
            SqlParameter[] parameters = {
            new SqlParameter("@vipId",vipInfo.vipId),
            new SqlParameter("@vipcard",vipInfo.vipcard),
            new SqlParameter("@vipname",vipInfo.vipname),
            new SqlParameter("@sex",vipInfo.sex),
            new SqlParameter("@phone",vipInfo.phone),
            new SqlParameter("@address",vipInfo.address),
            new SqlParameter("@registertime",vipInfo.registertime),
            new SqlParameter("@grade",vipInfo.grade),
            };
            return Dbhelper.ExecuteNonQueryProc(sql, parameters) > 0;


        }


        /// <summary>
        /// 删除会员信息 返回true：删除成功；返回false：删除失败 (proc)
        /// </summary>
        /// <param name="commodityID"></param>
        /// <returns></returns>
        public bool DeleteVipInfosoProc(int vipId)
        {
            string procName = @"PROC_DeleteVIPInfo";
            SqlParameter[] parameters = {
            new SqlParameter("@vipId",vipId)
            };
            return Dbhelper.ExecuteNonQueryProc(procName, parameters) > 0;
        }

        /// <summary>
        /// 根据vipId修改会员信息 返回true:修改成功；返回false：修改失败(proc)
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdatevipInfoProc(VipInfoModel model)
        {
            string sql = @"PROC_UpdateVIPInfo";

            SqlParameter[] parameters = {
            new SqlParameter("@vipcard",model.vipcard),
            new SqlParameter("@vipname",model.vipname),
            new SqlParameter("@sex",model.sex),
            new SqlParameter("@phone",model.phone),
            new SqlParameter("@address",model.address),
            new SqlParameter("@grade",model.grade),
            new SqlParameter("@vipId",model.vipId),
            };
            return Dbhelper.ExecuteNonQueryProc(sql, parameters) > 0;
        }


    }
}
